﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BLL
{
    public class StonesWithTypeName
    {
        public StonesWithTypeName(string sName, DAL.Stone stColl)
        {
            stones = stColl;
            stoneTypeName = sName;
        }

        public DAL.Stone stones { get; set; }
        public string stoneTypeName { get; set; }
    }
    public class Common
    {
        #region Constructor

        public Common()
        {

        }

        #endregion Constructor

        #region Common Methods

        public static List<DAL.Shape> GetShapes()
        {
            List<DAL.Shape> shapes = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varShapes = from s in dbContext.GetTable<DAL.Shape>()
                                where s.IsActive == true
                                select s;

                shapes = varShapes.ToList();
            }

            return shapes;
        }

        public static List<DAL.SideType> GetSideType()
        {
            List<DAL.SideType> sideType = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varSideType = from st in dbContext.GetTable<DAL.SideType>()
                                  where st.IsActive == true
                                  select st;

                sideType = varSideType.ToList();
            }

            return sideType;
        }

        public static List<DAL.ProjectType> GetProjectType()
        {
            List<DAL.ProjectType> projectType = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varProjectType = from pt in dbContext.GetTable<DAL.ProjectType>()
                                     where pt.IsActive == true
                                     select pt;

                projectType = varProjectType.ToList();
            }

            return projectType;
        }

        public static List<DAL.ProjectUrgency> GetProjectUrgency()
        {
            List<DAL.ProjectUrgency> projectUrgency = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varProjectUrgency = from pu in dbContext.GetTable<DAL.ProjectUrgency>()
                                        where pu.IsActive == true
                                        select pu;

                projectUrgency = varProjectUrgency.ToList();
            }

            return projectUrgency;
        }

        public static List<DAL.Edge> GetEdges()
        {
            List<DAL.Edge> edges = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varEdges = from e in dbContext.GetTable<DAL.Edge>()
                               where e.IsActive == true
                               select e;

                edges = varEdges.ToList();
            }

            return edges;
        }

        public static DAL.Edge GetEdgeById(int id)
        {
            DAL.Edge edge = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varEdge = from e in dbContext.GetTable<DAL.Edge>()
                              where e.IsActive == true &&
                              e.Id == id
                              select e;

                edge = varEdge.FirstOrDefault();
            }

            return edge;
        }

        public static List<DAL.Stone> GetStones()
        {
            List<DAL.Stone> stones = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStones = from s in dbContext.GetTable<DAL.Stone>()
                                where s.IsActive == true
                                select s;

                stones = varStones.ToList();
            }

            return stones;
        }

        public static List<DAL.GetStoneTypeWithPriceResult> GetStoneTypeWithPrice()
        {
            List<DAL.GetStoneTypeWithPriceResult> stoneType = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneType = dbContext.GetStoneTypeWithPrice();
                stoneType = varStoneType.ToList();
            }

            return stoneType;
        }

        public static DAL.GetStoneTypeWithPriceResult GetStoneTypeWithPriceByStoneType(int stoneTypeId)
        {
            DAL.GetStoneTypeWithPriceResult stoneType = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneType = from x in dbContext.GetStoneTypeWithPrice()
                                   where x.StoneTypeId == stoneTypeId
                                   select x;

                stoneType = varStoneType.FirstOrDefault();
            }

            return stoneType;
        }

        public static List<DAL.GetStoneTypeCategoryWithPriceResult> GetStoneTypeCategoryWithPrice(int stoneTypeId)
        {
            List<DAL.GetStoneTypeCategoryWithPriceResult> stoneTypeCategory = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneType = dbContext.GetStoneTypeCategoryWithPrice(stoneTypeId);
                stoneTypeCategory = varStoneType.ToList();
            }

            return stoneTypeCategory;

        }
        public static DAL.GetStoneTypeCategoryWithPriceScalarResult GetStoneTypeCategoryWithPriceScalar(int stoneTypeId, int stoneCatID)
        {
            DAL.GetStoneTypeCategoryWithPriceScalarResult price = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneType = dbContext.GetStoneTypeCategoryWithPriceScalar(stoneTypeId, stoneCatID);
                price = varStoneType.FirstOrDefault();
            }

            return price;

        }
        public static DAL.Stone GetStoneById(int stoneID)
        {
            DAL.Stone result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var objStone = from s in dbContext.GetTable<DAL.Stone>()
                               where s.IsActive == true &&
                               s.Id == stoneID
                               select s;

                result = objStone.FirstOrDefault();
            }

            return result;
        }

        public static List<DAL.Stone> GetStonesByStoneType(int stoneTypeID)
        {
            List<DAL.Stone> stones = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStones = from s in dbContext.GetTable<DAL.Stone>()
                                where s.IsActive == true &&
                                s.StoneTypeId == stoneTypeID
                                select s;

                stones = varStones.ToList();
            }

            return stones;
        }

        public static List<StonesWithTypeName> GetStonesByType_Name(int stoneTypeID, int stoneCategoryId)
        {
            DAL.StoneType stoneType = null;
            List<StonesWithTypeName> ss = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneTypes = from sType in dbContext.GetTable<DAL.StoneType>()
                                    where sType.IsActive == true && 
                                    sType.StoneTypeId == stoneTypeID
                                    select sType;
                stoneType = varStoneTypes.FirstOrDefault();
            }

            if (string.Compare(stoneType.Name.ToUpper(), "MARBLE", true) == 0)
            {
                using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
                {
                    var varStones = (from stonesColl in dbContext.GetTable<DAL.Stone>()
                                     join sT in dbContext.GetTable<DAL.StoneType>() on stonesColl.StoneTypeId equals sT.StoneTypeId
                                     where stonesColl.IsActive == true &&
                                     stonesColl.StoneCategoryId == stoneCategoryId &&
                                     (sT.Name == "Granite" || sT.Name == "Marble")
                                     orderby sT.Name descending, stonesColl.Price ascending
                                     select new StonesWithTypeName
                                     (
                                         sT.Name, stonesColl
                                     )
                                    );
                    ss = varStones.ToList();
                }
            }
            else if(string.Compare(stoneType.Name.ToUpper(), "GRANITE", true) == 0)
            {
                using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
                {
                    var varStones = (from stonesColl in dbContext.GetTable<DAL.Stone>()
                                     join sT in dbContext.GetTable<DAL.StoneType>() on stonesColl.StoneTypeId equals sT.StoneTypeId
                                     where stonesColl.IsActive == true &&
                                     stonesColl.StoneCategoryId == stoneCategoryId &&
                                     (sT.Name == "Granite" || sT.Name == "Marble") 
                                     orderby sT.Name, stonesColl.Price ascending 
                                     select new StonesWithTypeName
                                     (
                                         sT.Name, stonesColl
                                     )
                                    );
                    ss = varStones.ToList();
                }
            }
            else if (string.Compare(stoneType.Name.ToUpper(), "QUARTZ", true) == 0)
            {
                using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
                {
                    var varStones = (from stonesColl in dbContext.GetTable<DAL.Stone>()
                                     join sT in dbContext.GetTable<DAL.StoneType>() on stonesColl.StoneTypeId equals sT.StoneTypeId
                                     where stonesColl.IsActive == true &&
                                     sT.Name == "Quartz" && 
                                     stonesColl.StoneCategoryId == stoneCategoryId
                                     orderby stonesColl.Price ascending
                                     select new StonesWithTypeName
                                        (
                                            sT.Name, stonesColl
                                        )
                                            );
                    ss = varStones.ToList();
                }
            }
            return ss;
        }
        
        public static DAL.StoneType GetStoneTypeById(int stoneTypeID)
        {
            DAL.StoneType result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var objStoneType = from s in dbContext.GetTable<DAL.StoneType>()
                                   where s.IsActive == true &&
                                   s.StoneTypeId == stoneTypeID
                                   select s;

                result = objStoneType.FirstOrDefault();
            }

            return result;
        }

        public static List<DAL.StoneTypeCategory> GetStoneTypeCategoryByStoneId(int StoneTypeID)
        {
            List<DAL.StoneTypeCategory> result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var objStoneType = from s in dbContext.GetTable<DAL.StoneTypeCategory>()
                                   where s.IsActive == true &&
                                   s.StoneTypeId == StoneTypeID
                                   select s;

                result = objStoneType.ToList();
            }

            return result;
        }

        public static DAL.StoneCategory GetStoneCategoryById(int StoneCategoryID)
        {
            DAL.StoneCategory result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var objStoneType = from s in dbContext.GetTable<DAL.StoneCategory>()
                                   where s.IsActive == true &&
                                   s.StoneCategoryId == StoneCategoryID
                                   select s;

                result = objStoneType.FirstOrDefault();
            }

            return result;
        }


        public static List<DAL.StoneType> GetStoneType()
        {
            List<DAL.StoneType> stoneType = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneType = from st in dbContext.GetTable<DAL.StoneType>()
                                   where st.IsActive == true
                                   select st;

                stoneType = varStoneType.ToList();
            }

            return stoneType;
        }

        public static List<DAL.StoneCategory> GetStoneCategory()
        {
            List<DAL.StoneCategory> stoneCategory = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varStoneCategory = from sc in dbContext.GetTable<DAL.StoneCategory>()
                                       where sc.IsActive == true orderby sc.Name ascending
                                       select sc;

                stoneCategory = varStoneCategory.ToList();
            }

            return stoneCategory;
        }

        public static DAL.Price GetPrice()
        {
            DAL.Price price = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varPrice = from p in dbContext.GetTable<DAL.Price>()
                               where p.IsActive == true
                               select p;

                price = varPrice.FirstOrDefault();
            }

            return price;
        }

        public static List<DAL.SinkOption> GetSinkOption()
        {
            List<DAL.SinkOption> sinkOption = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varSinkOption = from so in dbContext.GetTable<DAL.SinkOption>()
                                    where so.IsActive == true
                                    select so;

                sinkOption = varSinkOption.ToList();
            }

            return sinkOption;
        }

        public static List<DAL.SinkOption> GetSinkOptionByCategoryId(int sinkOptionCategoryId)
        {
            List<DAL.SinkOption> sinkOption = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varSinkOption = from so in dbContext.GetTable<DAL.SinkOption>()
                                    where so.IsActive == true &&
                                    so.SinkOptionCategoryId == sinkOptionCategoryId
                                    select so;

                sinkOption = varSinkOption.ToList();
            }

            return sinkOption;
        }

        public static List<DAL.SinkOptionCategory> GetSinkOptionCategory()
        {
            List<DAL.SinkOptionCategory> sinkOptionCategory = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varSinkOptionCategory = from soc in dbContext.GetTable<DAL.SinkOptionCategory>()
                                            where soc.IsActive == true
                                            select soc;

                sinkOptionCategory = varSinkOptionCategory.ToList();
            }

            return sinkOptionCategory;
        }

        public static List<DAL.ZipSurcharge> GetZipSurcharge()
        {
            List<DAL.ZipSurcharge> zipSurcharge = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varZipSurcharge = from zs in dbContext.GetTable<DAL.ZipSurcharge>()
                                      where zs.IsActive == true
                                      select zs;

                zipSurcharge = varZipSurcharge.ToList();
            }

            return zipSurcharge;
        }

        public static int GetZipSurchargeByDistance(int distance)
        {
            DAL.ZipSurcharge zipSurcharge = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var varZipSurcharge = from zs in dbContext.GetTable<DAL.ZipSurcharge>()
                                      where zs.IsActive == true &&
                                      zs.MinDistance <= distance &&
                                      zs.MaxDistance >= distance
                                      select zs;

                zipSurcharge = varZipSurcharge.FirstOrDefault();
            }

            return zipSurcharge == null ? -1 : zipSurcharge.Surcharge;
        }

        #endregion Common Methods

        #region Presentation Layer Methods

        public static int AddUserDetails(string name, string email, string phoneNumber, int zipCode, bool isContacted,
            List<Entities.ShapeDimension> shapeDimensions, Entities.Options option, Entities.Cost cost)
        {
            int returnValue = 0;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.User record = new DAL.User();

                record.Name = name;
                record.Email = email;
                record.Phone = phoneNumber;
                record.ZipCode = zipCode;
                record.IsContactedByPhone = isContacted;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                dbContext.Users.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                returnValue = Convert.ToInt32(record.Id);

                bool isAddUserShapeDimension = AddUserShapeDimension(returnValue, shapeDimensions);
                bool isAddUserOptions = AddUserOptions(returnValue, option);
                bool isAddUserCost = AddUserCost(returnValue, cost);

                if (!(returnValue > 0 &&
                    isAddUserShapeDimension &&
                    isAddUserOptions &&
                    isAddUserCost))
                {
                    returnValue = 0;

                    if (returnValue > 0)
                    {
                        if (isAddUserShapeDimension)
                        {
                            Common.DeleteUserShapeDimension(returnValue);
                        }

                        if (isAddUserOptions)
                        {
                            Common.DeleteUserOptions(returnValue);
                        }

                        if (isAddUserCost)
                        {
                            Common.DeleteUserCost(returnValue);
                        }

                        var query = from u in dbContext.GetTable<DAL.User>()
                                    where u.Id == returnValue
                                    select u;

                        foreach (DAL.User item in query.ToList())
                        {
                            dbContext.Users.DeleteOnSubmit(item);
                        }

                        try
                        {
                            dbContext.SubmitChanges();
                        }
                        catch (Exception)
                        {

                        }
                    }
                }
            }

            return returnValue;
        }

        #endregion Presentation Layer Methods

        #region Admin Methods

        public static bool AuthenticateUser(string userName, string password)
        {
            bool retrunValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var user = from a in dbContext.GetTable<DAL.Admin>()
                           where a.UserName == userName &&
                           a.Password == password
                           select a;

                retrunValue = user.Count() > 0 ? true : false;
            }

            return retrunValue;
        }

        public static int AddShape(string name, string description, string thumbnail, string imagepath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.Shape record = new DAL.Shape();

                record.Name = name;
                record.Description = description;
                record.Thumbnail = thumbnail;
                record.ImagePath = imagepath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                dbContext.Shapes.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                returnValue = Convert.ToInt32(record.Id);
            }

            return returnValue;
        }

        public static bool SaveShape(int id, string name, string description, string thumbnail, string imagepath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.Shape record = dbContext.Shapes.Single(s => s.Id == id);

                record.Name = name;
                record.Description = description;
                record.Thumbnail = thumbnail;
                record.ImagePath = imagepath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                dbContext.SubmitChanges();
                returnValue = true;
            }

            return returnValue;
        }

        public static int AddSideType(int id, string name, string description, string imagePath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.SideType record = new DAL.SideType();

                record.SideTypeId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.SideTypes.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveSideType(int identity, int id, string name, string description, string imagePath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.SideType record = dbContext.SideTypes.Single(st => st.Id == identity);

                record.SideTypeId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    returnValue = true;
                    dbContext.SubmitChanges();
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        public static int AddEdge(string name, string description, string thumbnail, string imagepath, int price)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.Edge record = new DAL.Edge();

                record.Name = name;
                record.Description = description;
                record.Thumbnail = thumbnail;
                record.ImagePath = imagepath;
                record.Price = price;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                dbContext.Edges.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                returnValue = Convert.ToInt32(record.Id);
            }

            return returnValue;
        }

        public static bool SaveEdge(int identity, int id, string name, string description, string thumbnail, string imagepath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.Edge record = dbContext.Edges.Single(e => e.Id == identity);

                record.Name = name;
                record.Description = description;
                record.Thumbnail = thumbnail;
                record.ImagePath = imagepath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                dbContext.SubmitChanges();
                returnValue = true;
            }

            return returnValue;
        }

        public static int AddStoneTypeCategory(string imagepath, int stoneTypeId, int stoneCategoryId)
        {
            int returnValue;
            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.StoneTypeCategory record = new DAL.StoneTypeCategory();

                record.ImagePath = imagepath;
                record.StoneTypeId = stoneTypeId;
                record.StoneCategoryId = stoneCategoryId;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                dbContext.StoneTypeCategories.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                returnValue = Convert.ToInt32(record.Id);
            }

            return returnValue;
        }


        public static bool SaveStoneCategory(int id, string imagepath, int stoneTypeId, int stoneCategoryId, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.StoneTypeCategory record = dbContext.StoneTypeCategories.Single(s => s.Id == id);

                record.ImagePath = imagepath;
                record.StoneTypeId = stoneTypeId;
                record.StoneCategoryId = stoneCategoryId;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                dbContext.SubmitChanges();
                returnValue = true;
            }

            return returnValue;
        }



        public static int AddStone(string name, string description, string thumbnail, string imagepath, int stoneTypeId,
            int stoneCategoryId, string origin, string thickness, int price)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.Stone record = new DAL.Stone();

                record.Name = name;
                record.Description = description;
                record.Thumbnail = thumbnail;
                record.ImagePath = imagepath;
                record.StoneTypeId = stoneTypeId;
                record.StoneCategoryId = stoneCategoryId;
                record.Origin = origin;
                record.Thickness = thickness;
                record.Price = price;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                dbContext.Stones.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                returnValue = Convert.ToInt32(record.Id);
            }

            return returnValue;
        }

        public static bool SaveStone(int id, string name, string description, string thumbnail, string imagepath,
            int stoneTypeId, int stoneCategoryId, string origin, string thickness, int price, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.Stone record = dbContext.Stones.Single(s => s.Id == id);

                record.Name = name;
                record.Description = description;
                record.Thumbnail = thumbnail;
                record.ImagePath = imagepath;
                record.StoneTypeId = stoneTypeId;
                record.StoneCategoryId = stoneCategoryId;
                record.Origin = origin;
                record.Thickness = thickness;
                record.Price = price;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                dbContext.SubmitChanges();
                returnValue = true;
            }

            return returnValue;
        }

        public static int AddStoneType(int id, string name, string description, string imagePath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.StoneType record = new DAL.StoneType();

                record.StoneTypeId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.StoneTypes.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveStoneType(int identity, int id, string name, string imagePath, string description, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.StoneType record = dbContext.StoneTypes.Single(st => st.Id == identity);

                record.StoneTypeId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    dbContext.SubmitChanges();
                    returnValue = true;
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }
        
        public static int AddStoneCategory(int id, string name, string description, string imagePath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.StoneCategory record = new DAL.StoneCategory();

                record.StoneCategoryId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.StoneCategories.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveStoneCategory(int identity, int id, string name, string description, string imagePath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.StoneCategory record = dbContext.StoneCategories.Single(st => st.Id == identity);

                record.StoneCategoryId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    dbContext.SubmitChanges();
                    returnValue = true;
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        public static int AddProjectType(int id, string name, string description, string imagePath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.ProjectType record = new DAL.ProjectType();

                record.ProjectTypeId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.ProjectTypes.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveProjectType(int identity, int id, string name, string description, string imagePath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.ProjectType record = dbContext.ProjectTypes.Single(pt => pt.Id == identity);

                record.ProjectTypeId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    dbContext.SubmitChanges();
                    returnValue = true;
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        public static int AddProjectUrgency(int id, string name, string description, string imagePath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.ProjectUrgency record = new DAL.ProjectUrgency();

                record.ProjectUrgencyId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.ProjectUrgencies.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveProjectUrgency(int identity, int id, string name, string description, string imagePath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.ProjectUrgency record = dbContext.ProjectUrgencies.Single(pu => pu.Id == identity);

                record.ProjectUrgencyId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagePath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    dbContext.SubmitChanges();
                    returnValue = true;
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        public static int AddPrice(int backsplash, int sinkcutout, double tax)
        {
            int returnValue;

            if (GetPrice() != null && GetPrice().IsActive)
            {
                returnValue = 0;
            }
            else
            {
                using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
                {
                    DAL.Price record = new DAL.Price();

                    record.BackSplash = backsplash;
                    record.SinkCutOut = sinkcutout;
                    record.Tax = tax;
                    record.IsActive = true;
                    record.ModifiedDate = DateTime.Now;
                    record.CreationDate = DateTime.Now;

                    dbContext.Prices.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
            }


            return returnValue;
        }

        public static bool SavePrice(int backsplash, int sinkcutout, double tax, bool isActive)
        {
            bool returnValue = false;

            if (GetPrice().IsActive)
            {
                using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
                {
                    DAL.Price record = dbContext.Prices.Single(p => p.Id == GetPrice().Id);

                    record.BackSplash = backsplash;
                    record.SinkCutOut = sinkcutout;
                    record.Tax = tax;
                    record.IsActive = isActive;
                    record.ModifiedDate = DateTime.Now;

                    dbContext.SubmitChanges();
                    returnValue = true;
                }
            }

            return returnValue;
        }

        public static int AddSinkOption(int id, int sinkOptionCategoryId, string name, string description, string imagepath,
            int price)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.SinkOption record = new DAL.SinkOption();

                record.SinkOptionId = id;
                record.SinkOptionCategoryId = sinkOptionCategoryId;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagepath;
                record.Price = price;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.SinkOptions.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveSinkOption(int identity, int id, int sinkOptionCategoryId, string name, string imagepath,
            string description, int price, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.SinkOption record = dbContext.SinkOptions.Single(so => so.Id == identity);

                record.SinkOptionId = id;
                record.SinkOptionCategoryId = sinkOptionCategoryId;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagepath;
                record.Price = price;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    dbContext.SubmitChanges();
                    returnValue = true;
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        public static int AddSinkOptionCategory(int id, string name, string description, string imagepath)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.SinkOptionCategory record = new DAL.SinkOptionCategory();

                record.SinkOptionCategoryId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagepath;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                try
                {
                    dbContext.SinkOptionCategories.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    returnValue = Convert.ToInt32(record.Id);
                }
                catch (Exception)
                {
                    returnValue = -1;
                }
            }

            return returnValue;
        }

        public static bool SaveSinkOptionCategory(int identity, int id, string name, string description, string imagepath, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.SinkOption record = dbContext.SinkOptions.Single(so => so.Id == identity);

                record.SinkOptionCategoryId = id;
                record.Name = name;
                record.Description = description;
                record.ImagePath = imagepath;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                try
                {
                    dbContext.SubmitChanges();
                    returnValue = true;
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        public static int AddZipSurcharge(int minDistance, int maxDistance, int surcharge)
        {
            int returnValue;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.ZipSurcharge record = new DAL.ZipSurcharge();

                record.MinDistance = minDistance;
                record.MaxDistance = maxDistance;
                record.Surcharge = surcharge;
                record.IsActive = true;
                record.ModifiedDate = DateTime.Now;
                record.CreationDate = DateTime.Now;

                dbContext.ZipSurcharges.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                returnValue = Convert.ToInt32(record.Id);
            }

            return returnValue;
        }

        public static bool SaveZipSurcharge(int id, int minDistance, int maxDistance, int surcharge, bool isActive)
        {
            bool returnValue = false;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.ZipSurcharge record = dbContext.ZipSurcharges.Single(zs => zs.Id == id);

                record.MinDistance = minDistance;
                record.MaxDistance = maxDistance;
                record.Surcharge = surcharge;
                record.IsActive = isActive;
                record.ModifiedDate = DateTime.Now;

                dbContext.SubmitChanges();
                returnValue = true;
            }

            return returnValue;
        }

        public static List<DAL.GetUserOrderResult> GetUserOrder()
        {
            List<DAL.GetUserOrderResult> result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                result = dbContext.GetUserOrder().ToList();
            }

            return result;
        }

        public static List<DAL.GetUserShapeDimensionsResult> GetUserShapeDimensions()
        {
            List<DAL.GetUserShapeDimensionsResult> result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                result = dbContext.GetUserShapeDimensions().ToList();
            }

            return result;
        }

        public static List<DAL.GetUserShapeDimensionsResult> GetUserShapeDimensionsByUserId(int userId)
        {
            List<DAL.GetUserShapeDimensionsResult> result = null;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var query = from x in dbContext.GetUserShapeDimensions()
                            where x.Id == userId
                            select x;
                result = query.ToList();
            }

            return result;
        }

        #endregion Admin Methods

        #region Private Methods

        private static bool AddUserShapeDimension(int userId, List<Entities.ShapeDimension> shapeDimensions)
        {
            bool returnValue = true;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                foreach (Entities.ShapeDimension item in shapeDimensions)
                {
                    DAL.UserShapeDimension record = new DAL.UserShapeDimension();

                    record.UserId = userId;
                    record.ShapeId = item.shapeId;
                    record.Side = item.Side.ToString();
                    record.Measurement = item.Measurement;
                    record.SideTypeId = item.SideTypeId;
                    record.IsBackSplash = item.IsBackSplash;

                    dbContext.UserShapeDimensions.InsertOnSubmit(record);
                    dbContext.SubmitChanges();

                    if (Convert.ToInt32(record.Id) <= 0)
                    {
                        returnValue = false;
                    }
                }
            }

            return returnValue;
        }

        private static bool DeleteUserShapeDimension(int userId)
        {
            bool returnValue = true;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var query = from sd in dbContext.GetTable<DAL.UserShapeDimension>()
                            where sd.UserId == userId
                            select sd;

                foreach (DAL.UserShapeDimension item in query.ToList())
                {
                    dbContext.UserShapeDimensions.DeleteOnSubmit(item);
                }

                try
                {
                    dbContext.SubmitChanges();
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        private static bool AddUserOptions(int userId, Entities.Options option)
        {
            bool returnValue = true;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.UserOption record = new DAL.UserOption();

                record.UserId = userId;
                record.SinkOptionId = option.SinkOptionId == 0 ? (int?)null : option.SinkOptionId;
                record.ProjectUrgencyId = option.ProjectUrgencyId;
                record.ProjectTypeId = option.ProjectTypeId;
                record.Description = option.Description;
                record.EdgeId = option.EdgeId == 0 ? (int?)null : option.EdgeId;
                record.StoneId = option.StoneId;

                dbContext.UserOptions.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                if (Convert.ToInt32(record.Id) <= 0)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        private static bool DeleteUserOptions(int userId)
        {
            bool returnValue = true;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var query = from uo in dbContext.GetTable<DAL.UserOption>()
                            where uo.UserId == userId
                            select uo;

                foreach (DAL.UserOption item in query.ToList())
                {
                    dbContext.UserOptions.DeleteOnSubmit(item);
                }

                try
                {
                    dbContext.SubmitChanges();
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        private static bool AddUserCost(int userId, Entities.Cost cost)
        {
            bool returnValue = true;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                DAL.UserCost record = new DAL.UserCost();

                record.UserId = userId;
                record.Surface = Convert.ToInt32(cost.Surface);
                record.SurfacePrice = cost.SurfacePrice;
                record.Backsplash = cost.Backsplash;
                record.BacksplashPrice = cost.BacksplashPrice;
                record.Edge = cost.Edge;
                record.EdgePrice = cost.EdgePrice;
                record.SinkCutOut = cost.Sinkout;
                record.SinkCutOutPrice = cost.SinkoutPrice;
                record.ZipDistance = cost.ZipDistance;
                record.ZipSurcharge = cost.ZipSurcharge;
                record.Tax = Convert.ToInt32(cost.Tax);
                record.TotalPrice = cost.TotalPrice;

                dbContext.UserCosts.InsertOnSubmit(record);
                dbContext.SubmitChanges();

                if (Convert.ToInt32(record.Id) <= 0)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        private static bool DeleteUserCost(int userId)
        {
            bool returnValue = true;

            using (DAL.DataClasses1DataContext dbContext = new DAL.DataClasses1DataContext())
            {
                var query = from uc in dbContext.GetTable<DAL.UserCost>()
                            where uc.UserId == userId
                            select uc;

                foreach (DAL.UserCost item in query.ToList())
                {
                    dbContext.UserCosts.DeleteOnSubmit(item);
                }

                try
                {
                    dbContext.SubmitChanges();
                }
                catch (Exception)
                {
                    returnValue = false;
                }
            }

            return returnValue;
        }

        #endregion Private Methods

    }
}
